import { BaseModel } from './BaseModel';
import { query } from '../../config/database';

export interface IScript {
  id: string;
  name: string;
  content?: string;
  description?: string;
  tags: string[];
  folder_id?: string;
  created_at: Date;
  updated_at: Date;
  created_by?: string;
}

export class Script extends BaseModel {
  constructor() {
    super('scripts');
  }
  
  // 根据文件夹ID查找脚本
  async findByFolderId(folderId: string): Promise<IScript[]> {
    const result = await query(
      'SELECT * FROM scripts WHERE folder_id = $1 ORDER BY name ASC',
      [folderId]
    );
    return result.rows;
  }
  
  // 搜索脚本
  async search(searchQuery: {
    keyword?: string;
    tags?: string[];
    folderId?: string;
    createdBy?: string;
    limit?: number;
    offset?: number;
  }): Promise<{ scripts: IScript[]; total: number }> {
    let sql = 'SELECT * FROM scripts WHERE 1=1';
    let countSql = 'SELECT COUNT(*) as total FROM scripts WHERE 1=1';
    const params: any[] = [];
    let paramIndex = 1;
    
    // 关键字搜索（名称、描述、内容）
    if (searchQuery.keyword) {
      const keywordCondition = ` AND (
        name ILIKE $${paramIndex} OR 
        description ILIKE $${paramIndex} OR 
        content ILIKE $${paramIndex}
      )`;
      sql += keywordCondition;
      countSql += keywordCondition;
      params.push(`%${searchQuery.keyword}%`);
      paramIndex++;
    }
    
    // 标签搜索
    if (searchQuery.tags && searchQuery.tags.length > 0) {
      const tagsCondition = ` AND tags && $${paramIndex}`;
      sql += tagsCondition;
      countSql += tagsCondition;
      params.push(searchQuery.tags);
      paramIndex++;
    }
    
    // 文件夹过滤
    if (searchQuery.folderId) {
      const folderCondition = ` AND folder_id = $${paramIndex}`;
      sql += folderCondition;
      countSql += folderCondition;
      params.push(searchQuery.folderId);
      paramIndex++;
    }
    
    // 创建者过滤
    if (searchQuery.createdBy) {
      const creatorCondition = ` AND created_by = $${paramIndex}`;
      sql += creatorCondition;
      countSql += creatorCondition;
      params.push(searchQuery.createdBy);
      paramIndex++;
    }
    
    // 排序
    sql += ' ORDER BY updated_at DESC';
    
    // 分页
    if (searchQuery.limit) {
      sql += ` LIMIT $${paramIndex++}`;
      params.push(searchQuery.limit);
    }
    
    if (searchQuery.offset) {
      sql += ` OFFSET $${paramIndex++}`;
      params.push(searchQuery.offset);
    }
    
    // 执行查询
    // 计算countSql需要的参数数量（排除limit和offset）
    let countParamsLength = paramIndex - 1;
    if (searchQuery.limit) countParamsLength--;
    if (searchQuery.offset) countParamsLength--;
    
    const [scriptsResult, countResult] = await Promise.all([
      query(sql, params),
      query(countSql, params.slice(0, countParamsLength))
    ]);
    
    return {
      scripts: scriptsResult.rows,
      total: parseInt(countResult.rows[0].total, 10)
    };
  }
  
  // 获取脚本的完整信息（包含文件夹路径）
  async getScriptWithPath(id: string): Promise<any> {
    const result = await query(
      `SELECT 
        s.*,
        f.path as folder_path,
        f.name as folder_name
       FROM scripts s
       LEFT JOIN folders f ON s.folder_id = f.id
       WHERE s.id = $1`,
      [id]
    );
    return result.rows[0] || null;
  }
  
  // 检查脚本名称在同一文件夹下是否唯一
  async isNameUniqueInFolder(name: string, folderId?: string, excludeId?: string): Promise<boolean> {
    let sql = 'SELECT 1 FROM scripts WHERE name = $1';
    const params: any[] = [name];
    let paramIndex = 2;
    
    if (folderId) {
      sql += ` AND folder_id = $${paramIndex++}`;
      params.push(folderId);
    } else {
      sql += ` AND folder_id IS NULL`;
    }
    
    if (excludeId) {
      sql += ` AND id != $${paramIndex++}`;
      params.push(excludeId);
    }
    
    const result = await query(sql, params);
    return result.rows.length === 0;
  }
  
  // 获取用户的脚本统计
  async getUserScriptStats(userId: string): Promise<{
    total: number;
    byFolder: Array<{ folder_name: string; count: number }>;
    byTag: Array<{ tag: string; count: number }>;
  }> {
    // 总数统计
    const totalResult = await query(
      'SELECT COUNT(*) as total FROM scripts WHERE created_by = $1',
      [userId]
    );
    
    // 按文件夹统计
    const folderResult = await query(
      `SELECT 
        COALESCE(f.name, '未分类') as folder_name,
        COUNT(*) as count
       FROM scripts s
       LEFT JOIN folders f ON s.folder_id = f.id
       WHERE s.created_by = $1
       GROUP BY f.name
       ORDER BY count DESC`,
      [userId]
    );
    
    // 按标签统计
    const tagResult = await query(
      `SELECT 
        unnest(tags) as tag,
        COUNT(*) as count
       FROM scripts 
       WHERE created_by = $1 AND array_length(tags, 1) > 0
       GROUP BY tag
       ORDER BY count DESC
       LIMIT 10`,
      [userId]
    );
    
    return {
      total: parseInt(totalResult.rows[0].total, 10),
      byFolder: folderResult.rows,
      byTag: tagResult.rows
    };
  }
  
  // 批量更新脚本标签
  async updateTags(id: string, tags: string[]): Promise<boolean> {
    const result = await query(
      `UPDATE scripts 
       SET tags = $2, updated_at = CURRENT_TIMESTAMP
       WHERE id = $1`,
      [id, JSON.stringify(tags)]
    );
    return result.rowCount > 0;
  }
  
  // 移动脚本到其他文件夹
  async moveToFolder(id: string, folderId?: string): Promise<boolean> {
    const result = await query(
      `UPDATE scripts 
       SET folder_id = $2, updated_at = CURRENT_TIMESTAMP
       WHERE id = $1`,
      [id, folderId]
    );
    return result.rowCount > 0;
  }

  // 获取所有标签及其使用次数
  async getAllTags(): Promise<Array<{ tag: string; count: number }>> {
    const result = await query(
      `SELECT 
        unnest(tags) as tag,
        COUNT(*) as count
       FROM scripts 
       WHERE array_length(tags, 1) > 0
       GROUP BY tag
       ORDER BY count DESC, tag ASC`,
      []
    );
    
    return result.rows.map((row: any) => ({
      tag: row.tag,
      count: parseInt(row.count, 10)
    }));
  }
}